Setting a Lookup from a Workflow

One of the limitations of the workflow engine that I have found frustrating for a long time is the inability to dynamically set lookup fields based on the output of a FetchXML query, however I no longer have to worry as Alex Shlega has provided the answer to my problems with his TCS Tools Solution

I’ve used this tool for a few solutions in my environment and, after discussion with my good friend Megan Walker I realised it might be good to share a sample scenario.

The Scenario

There is a web form that is used by visitors to a website to submit queries.  The queries are added to CRM and are all FROM no-reply@company.com.  Within the body of the email is an email address for the submitter, and we need to extract the email address, find the related Contact and set a Lookup field (Regarding) to link the Contact to the Email.

The submitted email body has the following format:

[title] [Mr]
[first name] [Ben]
[last name] [Willer]
[email] [benwil@alliedholdingcompany.co.uk]
[phone] []
[address1] [Mounters]
[address2] [Marnhull]
[address3] [Sturminster Newton]
[address4] [Dorset]
[postcode] [DT10 1NR]
[how did you hear about us?] [Internet search]

 

The Solution

First things first, you will need to install the TCS Tools solution in your environment.  The link above will take you to Alex’s website to download the solution.  As ever, this is a free third-party tool, so install at your own risk.

Next, you will need to add a Single Line of Text field to your email entity to store the email address we’re going to extract from the body of the email above.  Rather imaginatively, I’ve named mine new_extractedemail.  We’ll need this schema name in the next step.

Create Lookup Configuration

Navigate to the TCS Lookup Configuration entity and create a new lookup configuration as follows:

The Entity Attribute should be the schema name of the lookup field you wish to set with your workflow.  In my case, I’m going to be setting the Regarding field on the Email, so I’ll be using regardingobjectid.

Next we need to create a Fetch XML expression to use in the Lookup Configuation.  The easiest way to do this is to create an advanced find, then download the Fetch XML.  For this one, I’m looking for a Contact where the Email Address equals the submitted email address, so my Advanced Find looks like this:

Note: as you can see above, I’ve set the Email to equal #new_extractedemail#.  The hashtags are used by the TCS Tools solution to replace this value dynamically.

The Fetch XML expression will look as follows:

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false”>
<entity name=”contact”>
<attribute name=”fullname” />
<attribute name=”telephone1″ />
<attribute name=”contactid” />
<order attribute=”fullname” descending=”false” />
<filter type=”and”>
<condition attribute=”emailaddress1″ operator=”eq” value=”#new_extractedemail#” />
</filter>
</entity>
</fetch>

Extract the Email Address

In order to be able to use the email address that was submitted above, we need to extract it from the the body.  I use Jason Lattimer’s Regex Extract step from his String Workflow Utilities workflow solution.  In order to extract the email address we need to do two Regex Extract steps, as follows:

Step 1: Extract Email Address from Body

The Regex Pattern in this step is (?<=\[email\] )([\s\S]*)(?=\[phone\] )

The pattern essentially looks for any characters in between the [email] and [phone] sections in the email body, and therefore the output from the email above is [benwil@alliedholdingcompany.co.uk].

In order to be able to use this in my workflow, we need to remove the square brackets, so I do another Regex Extract on the output of this step.

Step 2: Extract Email Address from within Square Brackets:

The Regex for this step is (?<=\[)([\s\S]*)(?=\]).  This pattern looks for any content in between the opening square bracket and the closing square bracket, so the output now is benwil@alliedholdingcompany.co.uk.

Note: I am not a Regex expert, but I have found Regex 101 invaluable in learning and testing my expressions, because it lets you see how the expression works and explains what each element means

Once we have carried out the Regex steps, we update the new_extractedemail field with the output of the second step:

Run the Lookup Setter

Now that we have the email address extracted and available on the Email entity, the last step is to run the TCS Lookup Configuration we created above to set the lookup:

 

The final workflow should look a bit like this:

 

Conclusion

This functionality is a really powerful addition to the workflow engine, and opens up a whole raft of advanced possibilities for CRM administrators to create workflows to solve complex problems.  I’ve used this internally to map Excluded Emails from ClickDimensions to error codes for the purposes of reporting, and I’m working on additional scenarios that we can use it for.